package com.wondertek.poms.dao.repo;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

import com.wondertek.poms.dao.po.EquityNode;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;
import sun.awt.SunHints;

import java.util.List;
import java.util.Optional;

/**
 * <p><b>IPcProductRuleDao Description:</b> 二级产品规则 Dao 执行二级产品规则 的查询、修改、新增、删除操作</p>
 * <p>@author 窦再兴</p>
 * <p><b>DATE</b> 2020-10-16 15:56:15</p>
 */
public interface IEquityNodeDao extends JpaRepository<EquityNode, Long>, JpaSpecificationExecutor<EquityNode> {

    @Query(value="select * from EQUITY_NODE en where en.TREE_ID = ?1 and en.PARENT_ID = 0 and (en.DELETE_STATUS <> 1 or en.DELETE_STATUS is null)", nativeQuery = true)
    List<EquityNode> getFirstNodesByTreeId(Long treeId);

    /**
     *<p><b>Title:</b> getValidFirstNodesByTreeId </p>
     *<p><b>Description:</b> 获取有效一级权益节点树 </p>
     *@author gaozhengxing gaozhenghang@wondertek.com.cn
     *@date 2021/2/7 15:43
     *@param treeId 树id
     */
    @Query(value="select * from EQUITY_NODE en where en.TREE_ID = ?1 and en.PARENT_ID = 0 and (en.DELETE_STATUS <> 1 or en.DELETE_STATUS is null)" +
            " and en.STATUS <> 1", nativeQuery = true)
    List<EquityNode> getValidFirstNodesByTreeId(Long treeId);

    @Query(value="select * from EQUITY_NODE en where en.PARENT_ID = ?1 and (en.DELETE_STATUS <> 1 or en.DELETE_STATUS is null)", nativeQuery = true)
    List<EquityNode> getNodeByParentId(Long id);

    /**
     *<p><b>Title:</b> getValidNodeByParentId </p>
     *<p><b>Description:</b> 根据父id获取子id </p>
     *@author gaozhengxing gaozhenghang@wondertek.com.cn
     *@date 2021/2/7 15:43
     *@param id 父id
     */
    @Query(value="select * from EQUITY_NODE en where en.PARENT_ID = ?1 and (en.DELETE_STATUS <> 1  or en.DELETE_STATUS is null)" +
            " and (en.STATUS <> 1 or en.STATUS is null)", nativeQuery = true)
    List<EquityNode> getValidNodeByParentId(Long id);

    /**
     *<p><b>Title:</b> findNodeByParentId </p>
     *<p><b>Description:</b> 根据父id查询 </p>
     *@author gaozhengxing gaozhenghang@wondertek.com.cn
     *@date 2021/2/4 17:45
     *@param parentId 父id
     *@return 列表
     */
    @Query(value="select * from EQUITY_NODE en where en.PARENT_ID = ?1 and en.STATUS = 0 and en.DELETE_STATUS <> 1", nativeQuery = true)
    List<EquityNode> findNodeByParentId(Long parentId);

    @Transactional
    @Query(value="update EQUITY_NODE en set en.DELETE_STATUS=1 where en.TREE_ID = ?1 and en.DELETE_STATUS <> 1", nativeQuery = true)
    void deleteAllByTreeId(Long treeId);

    /**
     *<p><b>Title:</b> selectCountById </p>
     *<p><b>Description:</b> 查询该权益节点下的有效数 </p>
     *@author gaozhengxing gaozhenghang@wondertek.com.cn
     *@date 2021/1/20 11:12
     *@param id 权益节点id
     *@return  有效数
     */
    @Query(value=" select count(*) from Equity_Node en,Equity_Tree et where en.id in (select max(enn.id) from Equity_Node enn where  enn.status = '0' group by enn.Equity_Node_Id) and en.tree_Id = et.id and et.ACTIVES_STATUS = '0' and en.id = ?1", nativeQuery = true)
    long selectCountById(Long id);

    /**
     *<p><b>Title:</b> selectCountById </p>
     *<p><b>Description:</b> 查询所有有效权益节点 </p>
     *@author gaozhengxing gaozhenghang@wondertek.com.cn
     *@date 2021/1/20 11:12
     *@return  有效权益节点
     */
    @Query(value=" select en.* from Equity_Node en,Equity_Tree et where en.id in (select max(enn.id) from Equity_Node enn where  enn.status = '0' group by enn.Equity_Node_Id) and en.tree_Id = et.id and et.ACTIVES_STATUS = '0'", nativeQuery = true)
    List<EquityNode> findAllValid();

    List<EquityNode> findAllByTreeIdAndDeleteStatus(Long id,Integer status);

    List<EquityNode> findAllByParentIdAndDeleteStatus(Long id,Integer status);

    Optional<EquityNode> findById(Long id);

    EquityNode findEquityNodeByEquityNodeId(String id);

    @Query(value="select * from EQUITY_NODE en where en.NAME = ?1 and en.DELETE_STATUS <> 1", nativeQuery = true)
    List<EquityNode> findAllByName(String name);

}
